package DAO;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Repository;

import POJO.DevList;
import POJO.Terminal;

@Repository
public class TerminalDao {
	
	@Autowired
	JdbcTemplate jdbcTemplate;
	
	ArrayList<Terminal> terminals;
	ArrayList<DevList> devLists;

	/**
	 * 放回所有终端
	 * @return null 如果没有终端
	 *         Terminal的ArrayList,如果有终端存在
	 */
	public ArrayList<Terminal> returnAll(){
		terminals=new ArrayList<Terminal>();
		String getAllTerminalsSql="SELECT * FROM terminal t , branch b ,agent a WHERE t.terminal_branch_id=b.branch_id and b.branch_agent_id=a.agent_id ";
		jdbcTemplate.query(getAllTerminalsSql, new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				Terminal terminal=new Terminal();
					terminal.setTerminalID(rs.getInt("terminal_id"));
					terminal.setBranch_Short_Addr(rs.getString("branch_shortAddr"));
					terminal.setTerminal_name(rs.getString("terminal_name"));
					terminal.setBranchID(rs.getInt("terminal_branch_id"));
					terminal.setPassword(rs.getString("terminal_pwd"));
					terminal.setAdminPhone(rs.getString("branch_linkNum"));
					terminal.setSmsPlatform(rs.getInt("branch_smsplatform"));
					terminal.setAdminPwd(rs.getString("terminal_adminPwd"));
					terminal.setAgentID(rs.getInt("agent_id"));
					terminal.setLoginTime(rs.getTimestamp("terminal_loginTime"));
					terminal.setUpload(rs.getInt("agent_uploadAnjie"));
					terminal.setFeedbackURL(rs.getString("agent_feedback_url"));
					terminal.setAgentParentID(rs.getInt("agent_parentID"));
					terminal.setCostType(rs.getString("branch_costType"));
					terminal.setStandardCost(rs.getBigDecimal("branch_standardCost"));
					terminal.setStandardDuration(rs.getInt("branch_standardDuration"));
					terminal.setIncreaseCost(rs.getBigDecimal("branch_increaseCost"));
					terminal.setIncreaseDuration(rs.getInt("branch_increaseDuration"));
				terminals.add(terminal);
			}
		});
		if(terminals.isEmpty()){
			return null;
		}
		return terminals;
	}
	
	public Terminal get(int terminalId){
		final Terminal terminal=new Terminal();
		String getTerminalInfoSQL="SELECT * FROM terminal t , branch b WHERE t.terminal_branch_id=b.branch_id and t.terminal_id=?";
		Object[] params=new Object[]{terminalId};
		jdbcTemplate.query(getTerminalInfoSQL,params, new RowCallbackHandler(){
			public void processRow(ResultSet rs) throws SQLException {
				terminal.setTerminalID(rs.getInt("terminal_id"));
				terminal.setBranch_Short_Addr(rs.getString("branch_shortAddr"));
				terminal.setTerminal_name(rs.getString("terminal_name"));
				terminal.setBranchID(rs.getInt("terminal_branch_id"));
				terminal.setPassword(rs.getString("terminal_pwd"));
				terminal.setAdminPhone(rs.getString("branch_linkNum"));
				terminal.setSmsPlatform(rs.getInt("branch_smsplatform"));
				terminal.setAdminPwd(rs.getString("terminal_adminPwd"));
				terminal.setLoginTime(rs.getTimestamp("terminal_loginTime"));
				terminal.setAbnormalTime(rs.getTimestamp("terminal_abnormalTime"));
			}
		});
		if(terminal.getTerminalID()==-1){
			//未查询到任何终端数据
			return null;
		}else{
			return terminal;
		}
	}
	/**
	 * 用于登录
	 * @param terminalId
	 * @param password
	 * @return
	 */
	public Terminal valifiy(int terminalId,String password){
		final Terminal terminal=new Terminal();
		String getTerminalInfoSQL="SELECT * FROM terminal t,branch b,agent a where t.terminal_branch_id=b.branch_id and b.branch_agent_id=a.agent_id and t.terminal_id=? and terminal_pwd=? and terminal_status=0";
		Object[] params=new Object[]{terminalId,password};
		System.out.println("termialId:"+terminalId);
		jdbcTemplate.query(getTerminalInfoSQL,params, new RowCallbackHandler(){
			public void processRow(ResultSet rs) throws SQLException {
				terminal.setTerminalID(rs.getInt("terminal_id"));
				terminal.setBranch_Short_Addr(rs.getString("branch_shortAddr"));
				terminal.setTerminal_name(rs.getString("terminal_name"));
				terminal.setBranchID(rs.getInt("terminal_branch_id"));
				terminal.setPassword(rs.getString("terminal_pwd"));
				terminal.setAdminPhone(rs.getString("branch_linkNum"));
				terminal.setAdminPwd(rs.getString("terminal_adminPwd"));
				terminal.setAgentID(rs.getInt("agent_id"));
				terminal.setLoginTime(rs.getTimestamp("terminal_loginTime"));
				terminal.setUpload(rs.getInt("agent_uploadAnjie"));
				terminal.setFeedbackURL(rs.getString("agent_feedback_url"));
				terminal.setAgentParentID(rs.getInt("agent_parentID"));
				terminal.setCostType(rs.getString("branch_costType"));
				terminal.setStandardCost(rs.getBigDecimal("branch_standardCost"));
				terminal.setStandardDuration(rs.getInt("branch_standardDuration"));
				terminal.setIncreaseCost(rs.getBigDecimal("branch_increaseCost"));
				terminal.setIncreaseDuration(rs.getInt("branch_increaseDuration"));
			}
		});
		if(terminal.getTerminalID()==-1){
			//未查询到任何终端数据
			return null;
		}else{
			return terminal;
		}

	}
	
	public void updateVersion(Terminal terminal){
		String updateVersionSql="UPDATE terminal SET terminal_barcodeStatus=?,terminal_cardReadStatus=?,terminal_touchStatus=?,terminal_networkType=?,terminal_networkIP=?,terminal_simcardNum=?,terminal_soft_ver=?,terminal_hard_ver=?,terminal_peripheral_status=?,terminal_peripheral_softVer=?,terminal_lock=?,terminal_check=?" +
				" WHERE `terminal_id`=?";
		Object[] params=new Object[]{terminal.getBarcodeStatus(),terminal.getCardReadStatus(),terminal.getTouchStatus(),terminal.getNetworkType(),terminal.getNetworkIP(),terminal.getSimcardNum(),terminal.getsVersion(),terminal.gethVersion(),terminal.getPeripheralStatus(),terminal.getPeripheralSoftVer(),terminal.getLock(),terminal.getCheck(),terminal.getTerminalID()};
		jdbcTemplate.update(updateVersionSql,params);
	}
	
	/**
	 * 更新设备待发送请求数
	 * @param terminal
	 * by zgy
	 */
	public void updateTosend(int terminalID,int toSend,Timestamp timestamp){
		String sql="update terminal t set t.terminal_toSend=?,t.terminal_abnormalTime=? where t.terminal_id=?";
		Object[] params=new Object[]{toSend,timestamp,terminalID};
		jdbcTemplate.update(sql,params);
	}
	
	/**
	 * 将3G上网卡使用情况存入netcard表
	 * @param leftBalance
	 * @param useFlow
	 * @param leftFlow
	 * @param terminal
	 * @return
	 */
	public boolean update3GFlow(String leftBalance,String useFlow,String leftFlow,Terminal terminal){
		String update3GFlowSql="INSERT INTO `netcard` "
				+"(`netcard_leftbalance`, `netcard_usedflow`, `netcard_leftflow`, `netcard_terminal_id`)" 
				+" VALUES (?,?,?,?);";
		Object[] params=new Object[]{leftBalance,useFlow,leftFlow,terminal.getTerminalID()};
		int result=jdbcTemplate.update(update3GFlowSql,params);
		if(result==1){
			return true;
		}else{
			return false;
		}
	}
	
	/**
	 * 更新登录时间
	 * @param terminal
	 * @return
	 */
	public boolean updateLoginTime(Terminal terminal){
		String updateLoginTimeSql="UPDATE `terminal` SET `terminal_loginTime`=? "+
				" WHERE `terminal_id`=?";
		Date date=new Date();
		Timestamp ts=new Timestamp(date.getTime()); 
		Object[] params=new Object[]{ts,terminal.getTerminalID()};
		int result=jdbcTemplate.update(updateLoginTimeSql,params);
		if(result!=0){
			return true;
		}else{
			return false;
		}
		
	}
	
	/**
	 * 放回终端的网点ID
	 * @param terminal
	 * @return
	 */
	public int getTerminalBranchId(Terminal terminal){
		String getTerminalBranchIdSql="SELECT terminal_branch_id FROM terminal where terminal_id=?";
		int branchId=-1;
		Object[] params=new Object[]{terminal.getTerminalID()};
		try {
			branchId=jdbcTemplate.queryForInt(getTerminalBranchIdSql,params);
		} catch (Exception e) {
			// TODO: handle exception
		}
		return branchId;
	}
	
	/**
	 * 查询配置的警告发送手机号码
	 * @param name
	 * @return
	 * by zgy
	 */
	public String getAlarmPhone(String name){
		String sql="SELECT value FROM config where name=?";
		String value = null;
		Object[] params=new Object[]{name};
		try {
			value=jdbcTemplate.queryForObject(sql, params,String.class);
		} catch (Exception e) {
			// TODO: handle exception
		}
		return value;
	}
	/**
	 * 放回终端的网点ID
	 * @param terminal
	 * @return
	 */
	public int getTerminalBranchId(int terminalID){
		String getTerminalBranchIdSql="SELECT terminal_branch_id FROM terminal where terminal_id=?";
		int branchId=-1;
		Object[] params=new Object[]{terminalID};
		try {
			branchId=jdbcTemplate.queryForInt(getTerminalBranchIdSql,params);
		} catch (Exception e) {
			// TODO: handle exception
		}
		return branchId;
	}
	/**
	 * 根据代理商ID返回设备列表
	 * @param agentId
	 * @return
	 */
	public ArrayList<DevList> getDevLisByAgentId(int agentId){
		devLists=new ArrayList<DevList>();
		String sql = "SELECT t.terminal_id,b.branch_addr,b.branch_shortAddr FROM branch b,terminal t where t.terminal_branch_id = b.branch_id and b.branch_agent_id = ? and t.terminal_status = 0 order by t.terminal_id;";
		Object[] params=new Object[]{agentId};
		jdbcTemplate.query(sql,params, new RowCallbackHandler(){
			public void processRow(ResultSet rs) throws SQLException {
				DevList devList = new DevList();
				devList.setDeviceSeq("YB"+DevList.toDeviceSeq(rs.getInt("terminal_id")));
				devList.setDevAddr(rs.getString("branch_addr"));
				devList.setDevShortAddr(rs.getString("branch_shortAddr"));
				devLists.add(devList);
			}
		});
		return devLists;
	}
}
